﻿using Blm.Utils.Extensions;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;

namespace VisionCloud.Utility
{
    /// <summary>
    /// excel帮助类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// table写入excel文件
        /// </summary>
        /// <param name="excelTable"></param>
        /// <returns></returns>
        public static string WriteToExcelFile(DataTable excelTable, string exportFilePath,
            string excelTemplatePath = "")
        {
            try
            {
                ISheet templateSheet = null;
                //先检查是否有模板文件,有的话读取模板数据
                if (!string.IsNullOrEmpty(excelTemplatePath))
                {
                    if (File.Exists(excelTemplatePath))
                    {
                        var ext = Path.GetExtension(excelTemplatePath);

                        IWorkbook templateWorkbook = null;
                        if (ext.Contains("xlsx"))
                        {
                            templateWorkbook = new XSSFWorkbook(new FileStream(excelTemplatePath, FileMode.Open));
                        }
                        else
                        {
                            templateWorkbook = new HSSFWorkbook(new FileStream(excelTemplatePath, FileMode.Open));
                        }

                        templateSheet = templateWorkbook.GetSheetAt(0);
                    }
                }

                var workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet();

                //日期样式
                var dateStyle = workbook.CreateCellStyle();
                var dateFormat = workbook.CreateDataFormat();
                dateStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd");

                //生成head行
                var headRow = sheet.CreateRow(0);
                var templateRow = templateSheet == null ? null : templateSheet.GetRow(0);
                for (var col = 0; col < excelTable.Columns.Count; col++)
                {
                    var headCell = headRow.CreateCell(col);

                    headCell.SetCellValue(excelTable.Columns[col].Caption);
                    if (templateRow != null)
                    {
                        sheet.SetColumnWidth(col, templateSheet.GetColumnWidth(col));

                        var templateCell = templateRow.GetCell(col);
                        headCell.SetCellType(templateCell.CellType);

                        var cellStyle = workbook.CreateCellStyle();
                        cellStyle.CloneStyleFrom(templateCell.CellStyle);
                        headCell.CellStyle = cellStyle;
                    }
                }

                for (var rows = 0; rows < excelTable.Rows.Count; rows++)
                {
                    var excelRow = sheet.CreateRow(rows + 1);

                    for (var cols = 0; cols < excelTable.Rows[rows].ItemArray.Length; cols++)
                    {
                        var excelCell = excelRow.CreateCell(cols);
                        var value = excelTable.Rows[rows][cols];
                        switch (value.GetType().ToString())
                        {
                            case "System.String":
                                excelCell.SetCellValue(value.ToString());
                                break;

                            case "System.DateTime":
                                excelCell.SetCellValue(Convert.ToDateTime(value));
                                excelCell.CellStyle = dateStyle;
                                break;

                            case "System.Boolean":
                                excelCell.SetCellValue(Convert.ToBoolean(value));
                                break;

                            default:
                                excelCell.SetCellValue(value.ToString());
                                break;
                        }
                    }
                }

                using (var fStream = new FileStream(exportFilePath, FileMode.Create))
                {
                    workbook.Write(fStream);
                }

                return exportFilePath;
            }
            catch (Exception ex)
            {
                throw new Exception("导出Excel文件异常", ex);
            }
        }

        /// <summary>
        /// excel文件读取到table
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataTable ReadToDataTable(string filePath)
        {
            var excelTable = new DataTable();

            try
            {
                if (File.Exists(filePath))
                {
                    var ext = Path.GetExtension(filePath);
                    IWorkbook workbook = null;

                    if (ext == ".xlsx" || ext == ".xls")
                    {
                        if (ext.Contains("xlsx"))
                        {
                            workbook = new XSSFWorkbook(new FileStream(filePath, FileMode.Open));
                        }
                        else
                        {
                            workbook = new HSSFWorkbook(new FileStream(filePath, FileMode.Open));
                        }
                    }
                    else
                    {
                        throw new ArgumentException("导入的Excel文件格式不正确");
                    }

                    if (workbook.NumberOfSheets > 0)
                    {
                        //第一页数据
                        var sheet = workbook.GetSheetAt(0);

                        //总列数
                        if (sheet.PhysicalNumberOfRows > 0)
                        {
                            var columns = sheet.GetRow(0).LastCellNum;

                            var headRow = sheet.GetRow(0);
                            for (var i = 0; i < columns; i++)
                            {
                                excelTable.Columns.Add(headRow.GetCell(i).ToString());
                            }

                            for (var i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
                            {
                                var dataRow = sheet.GetRow(i + 1);
                                if (dataRow == null)
                                {
                                    break;
                                }
                                var row = excelTable.NewRow();
                                for (int j = dataRow.FirstCellNum; j < columns; j++)
                                {
                                    var cellData = dataRow.GetCell(j);
                                    if (cellData == null)
                                    {
                                        row[j] = null;
                                        continue;
                                    }

                                    if (cellData.CellType == CellType.Numeric)
                                    {
                                        if (DateUtil.IsValidExcelDate(cellData.NumericCellValue) &&
                                            DateUtil.IsCellDateFormatted(cellData))
                                        {
                                            row[j] = cellData.DateCellValue.NullToString();
                                        }
                                        else
                                        {
                                            row[j] = cellData.NumericCellValue;
                                        }
                                    }
                                    else if (cellData.CellType == CellType.Boolean)
                                    {
                                        row[j] = cellData.BooleanCellValue;
                                    }
                                    else if (cellData.CellType == CellType.Formula)
                                    {
                                        var eva = new HSSFFormulaEvaluator(workbook);
                                        row[j] = eva.Evaluate(cellData).StringValue;
                                    }
                                    else if (cellData.CellType == CellType.Blank)
                                    {
                                        row[j] = string.Empty;
                                    }
                                    else
                                    {
                                        row[j] = cellData.StringCellValue;
                                    }
                                }

                                excelTable.Rows.Add(row);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error("上传excel文件，错误信息" + ex.Message + ex.StackTrace);
                throw new Exception($"Excel读取异常,请确认是使用从系统下载的excel模板来导入数据。");
            }

            return RemoveEmptyRow(excelTable);
        }

        private static DataTable RemoveEmptyRow(DataTable dt)
        {
            if (dt == null)
                return null;

            for (int i = dt.Rows.Count - 1; i >= 0; i--)
            {
                var dr = dt.Rows[i];
                bool isEmptyRow = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(dr[j].ToString().Trim()))
                    {
                        isEmptyRow = false;
                        break;
                    }
                }
                if (isEmptyRow)
                {
                    dt.Rows.RemoveAt(i);
                }
            }

            return dt;
        }
    }
}